#import "DBManager.h"

@implementation DBManager

SYNTHESIZE_SINGLETON_FOR_CLASS(DBManager);

-(id)init{
    self = [super init];
    if (self != nil) {
        dbName = @"coldjoke.sqlite";
        NSArray *path = NSSearchPathForDirectoriesInDomains(NSCachesDirectory,NSUserDomainMask,YES);
        NSString *cachesDirectory = [path objectAtIndex:0];
        NSString *dbPath = [cachesDirectory stringByAppendingPathComponent:dbName];
        
        NSString *curVersion = [[NSBundle mainBundle] objectForInfoDictionaryKey: (NSString *)kCFBundleVersionKey];
        NSUserDefaults *defaults =[NSUserDefaults standardUserDefaults];
        NSString *dbVersion = [defaults stringForKey:@"dbVersion"];
        Log(@"curVersion : %@ , dbVersion : %@",curVersion,dbVersion);
        if( ![dbVersion isEqualToString:curVersion] ){
            NSFileManager *fileManager = [NSFileManager defaultManager];
            [fileManager removeItemAtPath:dbPath error:nil];
            [defaults setObject:curVersion forKey:@"dbVersion"];
            Log(@"rebuild dbVersion : %@",curVersion);
        }
        dbQueue = [[FMDatabaseQueue databaseQueueWithPath:dbPath] retain];
    }
    return self;
}

- (void) dealloc
{
    PP_RELEASE(dbName);
    PP_RELEASE(dbQueue);
    [super dealloc];
}

#pragma mark -
#pragma mark -------Joke 相关db操作------

-(BOOL)createJokeTable:(NSString*)sort //笑话表
{
    __block BOOL result = NO;
    [dbQueue inDatabase:^(FMDatabase *pDb) {
        NSString *sqlCreate =[NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS joke_%@ (joke_index INTEGER, joke_id TEXT PRIMARY KEY, joke_time TEXT, joke_text TEXT, joke_img_url TEXT, joke_gif_data BLOB, joke_img_full BLOB, joke_img_thumbnail BLOB, good_num INTEGER, bad_num INTEGER, reply_num INTEGER, user_id INTEGER, user_name TEXT, user_icon_url TEXT, joke_read INTEGER, joke_liked INTEGER, joke_unliked INTEGER)",sort];
        result = [pDb executeUpdate:sqlCreate];
    }];
    return result;
}

#pragma mark -------------保存笑话--------

-(void)saveJoke:(NSString *)sort :(JokeModel *)joke
{
    NSData *gifData;
    if( joke.jokeGifData != nil ){
        gifData = joke.jokeGifData;
    }else{
        gifData = NULL;
    }
    
    NSData *imgFullData;
    if( joke.jokeImgFull != nil ){
        imgFullData = UIImagePNGRepresentation(joke.jokeImgFull);
    }else{
        imgFullData = NULL;
    }
    
    NSData *imgThumbData;
    if( joke.jokeImgThumbnail != nil ){
        imgThumbData = UIImagePNGRepresentation(joke.jokeImgThumbnail);
    }else{
        imgThumbData = NULL;
    }
    
    NSString *sqlInsert = [NSString stringWithFormat:@"INSERT INTO joke_%@ (joke_index, joke_id, joke_time, joke_text, joke_img_url, joke_gif_data, joke_img_full, joke_img_thumbnail, good_num, bad_num, reply_num, user_id, user_name, user_icon_url, joke_read, joke_liked, joke_unliked) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",sort];
    
    [dbQueue inDatabase:^(FMDatabase *pDb) {
        [pDb executeUpdate:sqlInsert, [NSNumber numberWithInt:joke.jokeIndex], joke.jokeId, joke.jokeTime, joke.jokeText, joke.jokeImgUrl, gifData, imgFullData, imgThumbData, [NSNumber numberWithInt: joke.goodNum],[NSNumber numberWithInt:joke.badNum],[NSNumber numberWithInt:joke.replyNum],[NSNumber numberWithInt:joke.userId],joke.userName,joke.userIconUrl,[NSNumber numberWithInt:joke.read],[NSNumber numberWithInt:joke.liked],[NSNumber numberWithInt:joke.unliked]];
    }];
}

-(BOOL)getJokeImgFull:(NSString *)sort :(JokeModel *)pJoke
{
    Log(@"getJokeImgFull start");
    __block BOOL result = NO;
    [dbQueue inDatabase:^(FMDatabase *pDb) {
        NSString *sqlSelect = [NSString stringWithFormat:@"SELECT joke_img_full FROM joke_%@ WHERE joke_id=%@",sort,pJoke.jokeId];
        FMResultSet *rs = [pDb executeQuery:sqlSelect];
        if ([rs next]) {
            NSData *dataFull = [rs dataForColumn:@"joke_img_full"];
            pJoke.jokeImgFull = [UIImage imageWithData:dataFull];
            [rs close];
            result = YES;
        } else {
            [rs close];
        }
    }];
    return result;
}

-(BOOL)getJokeImgThumb:(NSString *)sort :(JokeModel *)pJoke
{
    __block BOOL result = NO;
    [dbQueue inDatabase:^(FMDatabase *pDb) {
        NSString *sqlSelect = [NSString stringWithFormat:@"SELECT joke_img_thumbnail FROM joke_%@ WHERE joke_id=%@",sort,pJoke.jokeId];
        FMResultSet *rs = [pDb executeQuery:sqlSelect];
        if ([rs next]) {
            NSData *dataThumb = [rs dataForColumn:@"joke_img_thumbnail"];
            pJoke.jokeImgThumbnail = [UIImage imageWithData:dataThumb];
            [rs close];
            result = YES;
        } else {
            [rs close];
        }
    }];
    return result;
}

-(BOOL)getJokeImgGif:(NSString *)sort :(JokeModel *)pJoke
{
    Log(@"getJokeImgGif start");

    __block BOOL result = NO;
    [dbQueue inDatabase:^(FMDatabase *pDb) {
        NSString *sqlSelect = [NSString stringWithFormat:@"SELECT joke_img_full,joke_gif_data FROM joke_%@ WHERE joke_id=%@",sort,pJoke.jokeId];
        FMResultSet *rs = [pDb executeQuery:sqlSelect];
        if ([rs next]) {
            NSData *dataFull = [rs dataForColumn:@"joke_img_full"];
            pJoke.jokeImgFull = [UIImage imageWithData:dataFull];
            pJoke.jokeGifData = [rs dataForColumn:@"joke_gif_data"];
            [rs close];
            result  = YES;
        } else {
            [rs close];
        }
    }];
    return result;
}

-(BOOL)getJokeImgAll:(NSString *)sort :(JokeModel *)pJoke
{
    __block BOOL result = NO;
    [dbQueue inDatabase:^(FMDatabase *pDb) {
        NSString *sqlSelect = [NSString stringWithFormat:@"SELECT joke_img_full,joke_img_thumbnail,joke_gif_data FROM joke_%@ WHERE joke_id=%@",sort,pJoke.jokeId];
        FMResultSet *rs = [pDb executeQuery:sqlSelect];
        if ([rs next]) {
            NSData *dataFull = [rs dataForColumn:@"joke_img_full"];
            pJoke.jokeImgFull = [UIImage imageWithData:dataFull];
            NSData *dataThumb = [rs dataForColumn:@"joke_img_thumbnail"];
            pJoke.jokeImgThumbnail = [UIImage imageWithData:dataThumb];
            pJoke.jokeGifData = [rs dataForColumn:@"joke_gif_data"];
            [rs close];
            result = YES;
        } else {
            [rs close];
        }
    }];
    return result;
}

-(NSMutableArray *)getAllJokeSummary:(NSString *)sort
{
    NSMutableArray *res = [[[NSMutableArray alloc] init] autorelease];
    [dbQueue inDatabase:^(FMDatabase *pDb) {
        NSString *sqlSelect = [NSString stringWithFormat:@"SELECT joke_index, joke_id, joke_time, joke_text, joke_img_url, good_num, bad_num, reply_num, user_id, user_name, user_icon_url, joke_read, joke_liked, joke_unliked FROM joke_%@ ORDER BY joke_index",sort];
        FMResultSet *rs = [pDb executeQuery:sqlSelect];
        while ([rs next]) {
            JokeModel *tJoke = [[[JokeModel alloc] init] autorelease];
            tJoke.jokeIndex = [rs intForColumn:@"joke_index"];
            tJoke.jokeId = [rs stringForColumn:@"joke_id"];
            tJoke.jokeTime = [rs stringForColumn:@"joke_time"];
            tJoke.jokeText = [rs stringForColumn:@"joke_text"];
            tJoke.jokeImgUrl = [rs stringForColumn:@"joke_img_url"];
            tJoke.goodNum = [rs intForColumn:@"good_num"];
            tJoke.badNum = [rs intForColumn:@"bad_num"];
            tJoke.replyNum = [rs intForColumn:@"reply_num"];
            tJoke.userId = [rs intForColumn:@"user_id"];
            tJoke.userName = [rs stringForColumn:@"user_name"];
            tJoke.userIconUrl = [rs stringForColumn:@"user_icon_url"];
            tJoke.read = [rs boolForColumn:@"joke_read"];
            tJoke.liked = [rs intForColumn:@"joke_liked"];
            tJoke.unliked = [rs intForColumn:@"joke_unliked"];
            [res addObject:tJoke ];
//            Log(@"testtest %d - %@",tJoke.jokeIndex,tJoke.jokeId);
        }
        [rs close];
    }];
    return res;
}

-(void)updateJoke:(NSString *)sort :(JokeModel *)pJoke {
    NSString *sqlUp = [NSString stringWithFormat:@"UPDATE joke_%@ SET joke_index=%d, good_num=%d, bad_num=%d, reply_num=%d, joke_read=%d, joke_liked=%d, joke_unliked=%d WHERE joke_id=%@", sort, pJoke.jokeIndex, pJoke.goodNum, pJoke.badNum, pJoke.replyNum, pJoke.read, pJoke.liked, pJoke.unliked, pJoke.jokeId];
    
    [dbQueue inDatabase:^(FMDatabase *pDb) {
        [pDb executeUpdate:sqlUp];
    }];
}

-(void)deleteJokes:(NSString *)sort :(NSMutableArray *)ids
{
    if( ids==nil || ids.count== 0){
        Log(@"nothing to do");
        return;
    }
    NSMutableString *ids2Del = [NSMutableString stringWithFormat:@"%@",[ids objectAtIndex:0]];
    for( int i=1 ; i<ids.count ; ++i ) {
        [ids2Del appendString:@","];
        [ids2Del appendString:[ids objectAtIndex:i]];
    }
    
    NSString *sqlDel = [NSString stringWithFormat:@"DELETE FROM joke_%@ WHERE joke_id IN (%@)",sort,ids2Del];
    [dbQueue inDatabase:^(FMDatabase *pDb) {
        [pDb executeUpdate:sqlDel];
    }];
}

-(void)removeJokeLikeState:(NSString *)sort {
    NSString *sqlUp = [NSString stringWithFormat:@"UPDATE joke_%@ SET joke_liked=-1, joke_unliked=-1",sort];
    [dbQueue inDatabase:^(FMDatabase *pDb) {
        [pDb executeUpdate:sqlUp];
    }];
}

#pragma mark -
#pragma mark -------Topic 相关db操作------

-(BOOL)createTopicTable:(NSString*)sort //主题表
{
    __block BOOL result = NO;
    [dbQueue inDatabase:^(FMDatabase *pDb) {
        NSString *sqlCreate_list = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS topic_%@ (topic_id TEXT PRIMARY KEY,topic_time TEXT,topic_title TEXT,topic_text TEXT,topic_img_url TEXT ,topic_img BLOB)",sort];
        
        result = [pDb executeUpdate:sqlCreate_list];
    }];
    return result;
}

-(BOOL)saveTopic:(NSString *)sort :(TopicModel *)topic
{
//    Log(@"%@ %@ %@ %@",topic.topicId,topic.topicTime,topic.topicText,topic.topicTitle);
    NSData *topicImgData;
    if( topic.topicImg != nil ){
        topicImgData = UIImagePNGRepresentation(topic.topicImg);
    }else{
        topicImgData = NULL;
    }
    NSString *sqlInsert = [NSString stringWithFormat:@"INSERT INTO topic_%@ (topic_id, topic_time, topic_title, topic_text, topic_img_url,topic_img) VALUES (?,?,?,?,?,?)",sort];
    
    __block BOOL result = NO;
    [dbQueue inDatabase:^(FMDatabase *pDb) {
       result = [pDb executeUpdate:sqlInsert,  topic.topicId, topic.topicTime, topic.topicTitle, topic.topicText, topic.topicImgUrl, topicImgData];
    }];
    return result;
}

-(NSMutableArray *)getAllTopicSummary:(NSString *)sort {
    NSMutableArray *res = [[[NSMutableArray alloc] init] autorelease];
    [dbQueue inDatabase:^(FMDatabase *pDb) {
        NSString *sqlSelect = [NSString stringWithFormat:@"SELECT topic_id, topic_time, topic_title, topic_text, topic_img_url FROM topic_%@ ORDER BY topic_id ASC",sort];
        FMResultSet *rs = [pDb executeQuery:sqlSelect];
        while ([rs next]) {
            TopicModel *tTopic = [[TopicModel alloc] init];
            tTopic.topicId = [rs stringForColumn:@"topic_id"];
            tTopic.topicTime = [rs stringForColumn:@"topic_time"];
            tTopic.topicTitle = [rs stringForColumn:@"topic_title"];
            tTopic.topicText = [rs stringForColumn:@"topic_text"];
            tTopic.topicImgUrl = [rs stringForColumn:@"topic_img_url"];
            [res addObject:tTopic];
            [tTopic release];
        }
        [rs close];
    }];
    return res;
}

-(TopicModel *)getTopicDetailForSort:(NSString *)sort withId:(NSString *)topicId
{
    __block TopicModel *tTopicTmp = nil;
    [dbQueue inDatabase:^(FMDatabase *pDb) {
        NSString *sqlSelect = [NSString stringWithFormat:@"SELECT * FROM topic_%@ WHERE topic_id='%@'",sort,topicId];
        
        FMResultSet *rs = [pDb executeQuery:sqlSelect];
        if ([rs next]) {
            TopicModel *tTopic = [[[TopicModel alloc] init] autorelease];
            tTopic.topicId = [rs stringForColumn:@"topic_id"];
            tTopic.topicTime = [rs stringForColumn:@"topic_time"];
            tTopic.topicTitle = [rs stringForColumn:@"topic_title"];
            tTopic.topicText = [rs stringForColumn:@"topic_text"];
            tTopic.topicImgUrl = [rs stringForColumn:@"topic_img_url"];
            NSData *topicImgData = [rs dataForColumn:@"topic_img"];
            tTopic.topicImg = [UIImage imageWithData:topicImgData];
            [rs close];
            tTopicTmp = tTopic;
        } else {
            [rs close];
            tTopicTmp = nil;
        }
    }];
    
    Log(@"tTopicTmp :%@",tTopicTmp.topicTitle);
    return tTopicTmp;
}


-(BOOL)getTopicImg:(NSString *)sort :(TopicModel *)pTopic {
    __block BOOL result = NO;
    [dbQueue inDatabase:^(FMDatabase *pDb) {
        NSString *sqlSelect = [NSString stringWithFormat:@"SELECT topic_img FROM topic_%@ WHERE topic_id=%@",sort,pTopic.topicId];
        FMResultSet *rs = [pDb executeQuery:sqlSelect];
        if ([rs next]) {
            NSData *dataImg = [rs dataForColumn:@"topic_img"];
            pTopic.topicImg = [UIImage imageWithData:dataImg];
            [rs close];
            result = YES;
        } else {
            [rs close];
        }
    }];
    return result;
}

-(void)deleteTopics:(NSString *)sort :(NSMutableArray *)ids
{
    if( ids==nil || ids.count== 0){
        Log(@"DBManager nothing to do");
        return;
    }
    NSMutableString *ids2Del = [NSMutableString stringWithFormat:@"%@",[ids objectAtIndex:0]];
    for( int i=1 ; i<ids.count ; ++i ) {
        [ids2Del appendString:@","];
        [ids2Del appendString:[ids objectAtIndex:i]];
    }
    
    NSString *sqlDel = [NSString stringWithFormat:@"DELETE FROM topic_%@ WHERE topic_id IN (%@)",sort,ids2Del];
    [dbQueue inDatabase:^(FMDatabase *pDb) {
        [pDb executeUpdate:sqlDel];
    }];
}

#pragma mark -
#pragma mark -------清空所有表数据操作------
-(void)clearAllData
{
    NSString *sqlTruncate2 = [NSString stringWithFormat:@" DELETE FROM joke_all"];
    [dbQueue inDatabase:^(FMDatabase *pDb) {
        [pDb executeUpdate:sqlTruncate2];
    }];

    NSString *sqlTruncate3 = [NSString stringWithFormat:@" DELETE FROM joke_hot"];
    [dbQueue inDatabase:^(FMDatabase *pDb) {
        [pDb executeUpdate:sqlTruncate3];
    }];

    NSString *sqlTruncate4 = [NSString stringWithFormat:@" DELETE FROM joke_shit"];
    [dbQueue inDatabase:^(FMDatabase *pDb) {
        [pDb executeUpdate:sqlTruncate4];
    }];
    
    
    NSString *sqlTruncate1 = [NSString stringWithFormat:@" DELETE FROM topic_all"];
    [dbQueue inDatabase:^(FMDatabase *pDb) {
        [pDb executeUpdate:sqlTruncate1];
    }];
    
}


@end
